/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sysdrive.DAO;

import br.com.sysdrive.model.CategoriaServico;
import br.com.sysdrive.model.Servico;
import br.com.sysdrive.util.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author WillianFrias
 */
public class ServicoDAOImpl implements GenericDAO {

    private Connection conn;

    public ServicoDAOImpl() throws Exception {
        try {
            this.conn = ConnectionFactory.getConnection();
        } catch (Exception ex) {
            throw new Exception(ex.getMessage());
        }
    }

    @Override
    public Boolean cadastrar(Object object) {
        PreparedStatement stmt = null;
        Servico servico = (Servico) object;
        String sql = "insert into servico (descricao_servico, status_servico, id_categoria_servico) values (?,?,?);";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, servico.getDescricaoServico().toUpperCase());
            stmt.setString(2, servico.getStatusServico().toUpperCase());
            stmt.setInt(3, servico.getCategoriaServico().getIdCategoriaServico());
            stmt.execute();
            return true;
        } catch (SQLException ex) {
            System.out.println("Problemas no SQL ao Cadastrar Servico! Erro ServicoDAO:" + ex.getMessage());
            ex.printStackTrace();
            return false;
        } catch (Exception ex) {
            System.out.println("Problemas ao Cadastrar Servico! Erro ServicoDAO: " + ex.getMessage());
            ex.printStackTrace();
            return false;
        } finally {
            try {
                ConnectionFactory.closeConnection(conn, stmt);
            } catch (Exception ex) {
                System.out.println("Problema ao fechar conexão Cadastrar Servico! Erro: " + ex.getMessage());
            }
        }
    }

    @Override
    public List<Object> listar() {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Object> resultado = new ArrayList<Object>();
        String sql = "select s.*, c.nome_categoria_servico "
                + "from servico s, categoria_servico c "
                + "where s.id_categoria_servico = c.id_categoria_servico "
                + "order by s.descricao_servico;";
        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Servico servico = new Servico();
                servico.setIdServico(rs.getInt("id_servico"));
                servico.setDescricaoServico(rs.getString("descricao_servico"));
                servico.setStatusServico(rs.getString("status_servico"));

                CategoriaServico categoriaServico = new CategoriaServico();
                categoriaServico.setIdCategoriaServico(rs.getInt("id_categoria_servico"));
                categoriaServico.setNomeCategoriaServico(rs.getString("nome_categoria_servico"));

                servico.setCategoriaServico(categoriaServico);

                resultado.add(servico);
            }
        } catch (SQLException ex) {
            System.out.println("Problemas no SQL do Listar Servico! Erro ServicoDAO: " + ex.getMessage());
        } finally {
            try {
                ConnectionFactory.closeConnection(conn, stmt, rs);
            } catch (Exception ex) {
                System.out.println("Problemas ao fechar os parâmetros de conexão! Erro ServicoDAO: " + ex.getMessage());
            }
            return resultado;
        }
    }

    @Override
    public Object carregar(int idObject) {
        PreparedStatement stmt = null;
        Servico servico = null;
        ResultSet rs = null;
        String sql = "select s.* from servico s where id_servico =?";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idObject);
            rs = stmt.executeQuery();
            while (rs.next()) {
                servico = new Servico();
                servico.setIdServico(rs.getInt("id_servico"));
                servico.setDescricaoServico(rs.getString("descricao_servico"));
                servico.setStatusServico(rs.getString("status_servico"));

                CategoriaServico categoriaServico = new CategoriaServico();
                categoriaServico.setIdCategoriaServico(rs.getInt("id_categoria_servico"));

                servico.setCategoriaServico(categoriaServico);
            }
            return servico;
        } catch (SQLException ex) {
            System.out.println("Problemas no SQL ao Carregar Servico! Erro ServicoDAO: " + ex.getMessage());
            ex.printStackTrace();
            return null;
        } finally {
            try {
                ConnectionFactory.closeConnection(conn, stmt, rs);
            } catch (Exception ex) {
                System.out.println("Problemas ao fechar os parâmetros de conexão Carregar Servico! Erro ServicoDAO: " + ex.getMessage());
                ex.printStackTrace();
            }
        }
    }

    @Override
    public Boolean alterar(Object object) {
        PreparedStatement stmt = null;
        Servico servico = (Servico) object;
        String sql = "update servico set descricao_servico = ?, status_servico = ?, id_categoria_servico = ?"
                + " where id_servico = ?";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, servico.getDescricaoServico().toUpperCase());
            stmt.setString(2, servico.getStatusServico().toUpperCase());
            stmt.setInt(3, servico.getCategoriaServico().getIdCategoriaServico());
            stmt.setInt(4, servico.getIdServico());
            stmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("Problemas no SQL ao Alterar Servico! Erro ServicoDAO: " + ex.getMessage());
            return false;
        } finally {
            try {
                ConnectionFactory.closeConnection(conn, stmt);
            } catch (Exception ex) {
                System.out.println("Problemas ao fechar parâmetros de conexão Alterar Servico! Erro ServicoDAO: " + ex.getMessage());
            }
        }
    }
}
